<?php


require_once(dirname(__FILE__)."/../common/log.php");
require_once(dirname(__FILE__)."/../common/defines.php");
require_once(dirname(__FILE__)."/mysql.config.php");


class mysqldb
{
    private $connect  = NULL;
	private $options  = 
    [
        'host'    => MYSQL_HOST,
        'port'    => MYSQL_PORT,
        'user'    => MYSQL_USER,
        'pwd'     => MYSQL_PWD,
        'dbname'  => MYSQL_DBNAME
    ];


    function __construct()
    {
    }

    function __destruct()
    {
        $this->close_db();
    }

    /**
     * 连接MYSQL函数,通过常量的形式来连接数据库
     * 自定义配置文件，配置文件中自定义常量，包含需要使用的信息
     * @return resource
     */
    function open_db($options = [])
    {
        if (!empty($options))
        {
            $this->options = array_merge($this->options, $options);
        } 

        try
        {
            $host    = $this->options['host'];
            $port    = $this->options['port'];
            $user    = $this->options['user'];
            $pwd     = $this->options['pwd'];
            $dbname  = $this->options['dbname'];

            $this->connect  = new mysqli($host, $user, $pwd, $dbname, $port);
            if (mysqli_connect_errno())
            {
                $this->connect  = NULL;

                loge("db connect failed: [".mysqli_connect_errno()."] ".mysqli_connect_error());
            }
            else
            {
                $this->connect->set_charset('utf8mb4');
            }
        }
        catch (Exception $e)
        {
            $this->connect  = NULL;

            loge($e->getMessage());

            return FAILURE;
        }

        return SUCCESS;
    }

    /**断开MYSQL
     * @param resource $link
     * @return boolean
     */
    function close_db()
    {
		if ($this->connect == NULL)
		{
            loge("db connect is error!");

			return FAILURE;
		}

        if (mysqli_close($this->connect) == false)
        {
            loge("db close failed: ".$this->get_error());

            return FAILURE;
        }

        return SUCCESS;
    }

    function get_connnect()
    {
        return $this->connect;
    }

    function get_error()
    {
		if ($this->connect == NULL)
		{
			return "connect is null";
		}

        return mysqli_error($this->connect);
    }

    function start_transaction()
    {
		if ($this->connect == NULL)
		{
            loge("db connect is error!");

			return FAILURE;
		}

        if (mysqli_autocommit($this->connect, FALSE) == false)
        {
            loge("db start transaction failed: ".$this->get_error());

            return FAILURE;
        }

        return SUCCESS;
    }

    function commit_transaction()
    {
		if ($this->connect == NULL)
		{
            loge("db connect is error!");

			return FAILURE;
		}

        if (mysqli_commit($this->connect) == false)
        {
            loge("db commit transaction failed: ".$this->get_error());

            return FAILURE;
        }

        return SUCCESS;
    }

    function finish_transaction()
    {
		if ($this->connect == NULL)
		{
            loge("db connect is error!");

			return FAILURE;
		}

        if (mysqli_autocommit($this->connect, TRUE) == false)
        {
            loge("db finish transaction failed: ".$this->get_error());

            return FAILURE;
        }

        return SUCCESS;
    }

    function rollback_transaction()
    {
		if ($this->connect == NULL)
		{
            loge("db connect is error!");

			return FAILURE;
		}

        if (mysqli_rollback($this->connect) == false)
        {
            loge("db rollback failed: ".$this->get_error());

            return FAILURE;
        }

        return SUCCESS;
    }

    /**
     * 读操作
     * @param string $sql
     * @param string $result_type
     * @return 成功时返回查询到的记录，失败时返回 false
     */
    function read($sql, $result_type=MYSQLI_ASSOC)
    {
        if ($this->connect == NULL)
        {
            loge("db connect is error!");

            return FAILURE;
        }

        try
        {
            $result  = mysqli_query($this->connect, $sql);
            if ($result === false)
            {
                loge("db query failed: ".$this->get_error());
                loge("db query failed: ".$sql);

                return FAILURE;
            }

            if (mysqli_num_rows($result) > 0)
            {
                while ($row = mysqli_fetch_array($result, $result_type))
                {
                    $rows[]  = $row;
                }

                mysqli_free_result($result);

                return $rows;
            }
            else
            {
                return [];
            }
        }
        catch (Exception $e)
        {
            loge("db query failed: ".$this->get_error());
            loge("db query failed: ".$sql);

            return FAILURE;
        }
    }

    /**
     * 写操作
     * @param string $sql
     * @return 成功时返回受影响的行数，失败时返回 false
     */
    function write($sql)
    {
        if ($this->connect == NULL)
        {
            loge("db connect is error!");

            return FAILURE;
        }

        try
        {
            $result  = mysqli_query($this->connect, $sql);
            if ($result == false)
            {
                loge("db delete failed: ".$this->get_error());
                loge("db delete failed: $sql");

                return FAILURE;
            }

            return mysqli_affected_rows($this->connect);
        }
        catch (Exception $e)
        {
            loge("db delete failed: ".$this->get_error());
            loge("db delete failed: ".$sql);

            return FAILURE;
        }
    }

    /**
     * 插入记录的操作
     * @param array $array
     * @param string $table
     * @return 成功时返回新插入记录的ID号，失败时返回 false
     */
    function insert($table, $array)
    {
		if ($this->connect == NULL)
		{
            loge("db connect is error!");

			return FAILURE;
		}

        try
        {
			$vals  = '';

			foreach (array_values($array) as $val)
			{
				if (is_string($val))
				{
					$vals  = $vals."'".$val."',";
				}
				else
				{
					$vals  = $vals.$val.',';
				}
			}

            $keys  = '`'.join('`,`', array_keys($array)).'`';
            $vals  = rtrim($vals, ',');
            $sql   = "INSERT {$table}({$keys}) VALUES ({$vals})";

            $result  = mysqli_query($this->connect, $sql);
            if ($result == false)
            {
                loge("db insert failed for $table: ".$this->get_error());
                loge("db insert failed for $table: $sql");

                return FAILURE;
            }

            return mysqli_insert_id($this->connect);
        }
        catch (Exception $e)
        {
            loge("db insert failed for $table: ".$this->get_error());
            loge("db insert failed for $table: ".$sql);

            return FAILURE;
        }
    }

    /**
     * MYSQL更新操作
     * @param array $array
     * @param string $table
     * @param string $where
     * @return 成功时返回受影响的行数，失败时返回 false
     */
    function update($table, $array, $where=NULL)
    {
		if ($this->connect == NULL)
		{
            loge("db connect is error!");

			return FAILURE;
		}

        try
        {
            if ($where == NULL)
            {
                loge("db update failed for $table, where field required !");

                return FAILURE;
            }

			$sets  = "";
            foreach ($array as $key => $val)
            {
                if (is_array($val))
                {
                    $sets .= "$key=".$val[0].",";
                }
                else if (is_string($val))
                {
                    $sets .= "$key='$val',";
                }
                else
                {
                    $sets .= "$key=$val,";
                }
            }

            $sets    = rtrim($sets, ','); //去掉SQL里的最后一个逗号
            $sql     = "UPDATE {$table} SET {$sets} WHERE {$where}";
            $result  = mysqli_query($this->connect, $sql);
            if ($result == false)
            {
                loge("db update failed for $table: ".$this->get_error());
                loge("db update failed for $table: $sql");

                return FAILURE;
            }

            return mysqli_affected_rows($this->connect);
        }
        catch (Exception $e)
        {
            loge("db update failed for $table: ".$this->get_error());
            loge("db update failed for $table: ".$sql);

            return FAILURE;
        }
    }

    /**
     * 删除记录的操作
     * @param string $table
     * @param string $where
     * @return 成功时返回受影响的行数，失败时返回 false
     */
    function delete($table, $where=NULL)
    {
		if ($this->connect == NULL)
		{
            loge("db connect is error!");

			return FAILURE;
		}

        try
        {
            if ($where == NULL)
            {
                loge("db delete failed for $table, where field required !");
                loge("db delete failed for $table: $sql");

                return FAILURE;
            }

            $sql     = "DELETE FROM {$table} WHERE {$where}";
            $result  = mysqli_query($this->connect, $sql);
            if ($result == false)
            {
                loge("db delete failed for $table: ".$this->get_error());
                loge("db delete failed for $table: $sql");

                return FAILURE;
            }

            return mysqli_affected_rows($this->connect);
        }
        catch (Exception $e)
        {
            loge("db delete failed for $table: ".$this->get_error());
            loge("db delete failed for $table: ".$sql);

            return FAILURE;
        }
    }

    /**
     * 检查是否存在指定记录
     * @param string $sql
     * @param string $result_type
     * @return 找到则返回 true，没找到或失败时返回 false
     */
    function exist($table, $where=NULL, $result_type=MYSQLI_ASSOC)
    {
		if ($this->connect == NULL)
		{
            loge("db connect is error!");

			return FAILURE;
		}

        try
        {
            if ($where == NULL)
            {
                loge("db exist failed for $table, where field required !");

				return FAILURE;
            }
            else
            {
                $sql  = "SELECT * FROM {$table} WHERE {$where}";
            }

            $result  = mysqli_query($this->connect, $sql);
            if ($result !== false)
            {
                $num  = mysqli_num_rows($result);

                mysqli_free_result($result);

                return $num;
            }
            else
            {
                loge("db query failed for $table: ".$this->get_error());
                loge("db query failed for $table: $sql");

                return FAILURE;
            }
        }
        catch (Exception $e)
        {
            loge("db query failed for $table: ".$this->get_error());
            loge("db query failed for $table: ".$sql);

            return FAILURE;
        }
    }

    /**
     * 查询一条记录
     * @param string $sql
     * @param string $result_type
     * @return 成功时返回查询到的记录，失败时返回 false
     */
    function find($table, $sets="*", $where=NULL, $result_type=MYSQLI_ASSOC)
    {
		if ($this->connect == NULL)
		{
            loge("db connect is error!");

			return FAILURE;
		}

        try
        {
            if ($where == NULL)
            {
                $sql  = "SELECT {$sets} FROM {$table} limit 1";
            }
            else
            {
                $sql  = "SELECT {$sets} FROM {$table} WHERE {$where} limit 1";
            }

            $result  = mysqli_query($this->connect, $sql);
			if ($result === false)
			{
				loge("db query failed for $table: ".$this->get_error());
				loge("db query failed for $table: ".$sql);

				return FAILURE;
			}

            if (mysqli_num_rows($result) > 0)
            {
                $row  = mysqli_fetch_array($result, $result_type);

                mysqli_free_result($result);

                return $row;
            }
            else
            {
                return [];
            }
        }
        catch (Exception $e)
        {
            loge("db query failed for $table: ".$this->get_error());
            loge("db query failed for $table: ".$sql);

            return FAILURE;
        }
    }

    /**
     * 得到表中的所有记录
     * @param string $sql
     * @param string $result_type
     * @return 成功时返回查询到的记录，失败时返回 false
     */
    function select($table, $sets="*", $where=NULL, $result_type=MYSQLI_ASSOC)
    {
		if ($this->connect == NULL)
		{
            loge("db connect is error!");

			return FAILURE;
		}

        try
        {
            if ($where == NULL)
            {
                $sql  = "SELECT {$sets} FROM {$table}";
            }
            else
            {
                $sql  = "SELECT {$sets} FROM {$table} WHERE {$where}";
            }

            $result  = mysqli_query($this->connect, $sql);
			if ($result === false)
			{
				loge("db query failed for $table: ".$this->get_error());
				loge("db query failed for $table: ".$sql);

				return FAILURE;
			}

            if (mysqli_num_rows($result) > 0)
            {
                while ($row = mysqli_fetch_array($result, $result_type))
                {
                    $rows[]  = $row;
                }

                mysqli_free_result($result);

                return $rows;
            }
            else
            {
                return [];
            }
        }
        catch (Exception $e)
        {
            loge("db query failed for $table: ".$this->get_error());
            loge("db query failed for $table: ".$sql);

            return FAILURE;
        }
    }

    /**取得结果集中的记录的条数
     * @param string $sql
     * @return number|boolean
     */
    function count($table, $where=NULL)
    {
		if ($this->connect == NULL)
		{
            loge("db connect is error!");

			return FAILURE;
		}

        try
        {
            if ($where == NULL)
            {
                $sql  = "SELECT * FROM {$table}";
            }
            else
            {
                $sql  = "SELECT * FROM {$table} WHERE {$where}";
            }

            $result  = mysqli_query($this->connect, $sql);
            if ($result !== false)
            {
                $num  = mysqli_num_rows($result);

                mysqli_free_result($result);

                return $num;
            }
            else
            {
                loge("db query failed for $table: ".$this->get_error());
                loge("db query failed for $table: $sql");

                return FAILURE;
            }
        }
        catch (Exception $e)
        {
            loge("db query failed for $table: ".$this->get_error());
            loge("db query failed for $table: ".$sql);

            return FAILURE;
        }
    }
}

